using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SQLite;
using FluorineFx;
using FluorineFx.AMF3;

namespace SharedCloud
{
    public class Cloud
    {
        protected string tablePrefix;
        protected SQLiteConnection conn;

        public Cloud(string tablePrefix)
        {
            this.tablePrefix = tablePrefix;
            conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["SQLite"].ConnectionString);
            CreateTables();
        }

        protected void CreateTables()
        {
            try
            {
                string sql = String.Format("CREATE TABLE {0}_Objects (cid NUMERIC, lid NUMERIC, remoteType TEXT)", tablePrefix);
                ExecuteNQ(sql);
            }
            catch (Exception) { }

            try
            {
                string sql = String.Format("CREATE TABLE {0}_Props (cid NUMERIC, lid NUMERIC, propName TEXT, propVal TEXT)", tablePrefix);
                ExecuteNQ(sql);
            }
            catch (Exception) { }

            try
            {
                string sql = String.Format("CREATE TABLE {0}_Refs (itemIndex NUMERIC, cid NUMERIC, lid NUMERIC, propName TEXT, sourceCid NUMERIC, sourceLid NUMERIC)", tablePrefix);
                ExecuteNQ(sql);
            }
            catch (Exception) { }
        }

        public List<ASObject> FetchAll()
        {
            List<ASObject> updates = new List<ASObject>();
            string sql = String.Format("SELECT * FROM {0}_Objects", tablePrefix);
            DataTable objs = ExecuteDT(sql);
            foreach (DataRow objDR in objs.Rows)
            {
                int cid = Convert.ToInt32(objDR["cid"]);
                int lid = Convert.ToInt32(objDR["lid"]);
                string remoteType = objDR["remoteType"].ToString();

                ASObject header = new ASObject("SharedCloud::SyncHeader");
                header["remoteType"] = remoteType;
                header["incomingRefs"] = FetchIncomingRefs(cid, lid);
                header["vo"] = FetchVO(cid, lid);
                updates.Add(header);
            }
            return updates;
        }
        protected ArrayCollection FetchIncomingRefs(int cid, int lid)
        {
            ArrayCollection incomingRefs = new ArrayCollection();
            string sql = String.Format("SELECT * FROM {0}_Refs WHERE cid={1} AND lid={2}",
                                        tablePrefix, cid, lid);
            DataTable refs = ExecuteDT(sql);
            foreach (DataRow refDR in refs.Rows)
            {
                ASObject refASO = new ASObject("SharedCloud::Ref");
                refASO["clientID"] = refDR["sourceCid"];
                refASO["localID"] = refDR["sourceLid"];
                refASO["property"] = refDR["propName"];
                refASO["index"] = refDR["itemIndex"];
                incomingRefs.Add(refASO);
            }
            return incomingRefs;
        }
        protected ASObject FetchVO(int cid, int lid)
        {
            ASObject vo = new ASObject();
            vo["clientID"] = cid;
            vo["localID"] = lid;

            string sql = String.Format("SELECT * FROM {0}_Props WHERE cid={1} AND lid={2}",
                                        tablePrefix, cid, lid);
            DataTable props = ExecuteDT(sql);
            foreach (DataRow propDR in props.Rows)
            {
                vo[propDR["propName"]] = propDR["propVal"];
            }
            return vo;
        }

        public void Persist(ASObject header)
        {
            ASObject vo = header["vo"] as ASObject;
            int cid = Convert.ToInt32(vo["clientID"]);
            int lid = Convert.ToInt32(vo["localID"]);

            //insert the object if it doesn't exist
            string sql = String.Format("SELECT * FROM {0}_Objects WHERE cid={1} AND lid={2}",
                                    tablePrefix, cid, lid);
            if (ExecuteDT(sql).Rows.Count == 0) //if object doesnt exist
            {
                string insSql = String.Format("INSERT INTO {0}_Objects (cid,lid,remoteType) " +
                                "VALUES ({1},{2},{3})", tablePrefix, cid, lid, MakeDBSafe(header["remoteType"]));
                ExecuteNQ(insSql);
            }

            PersistRefs(cid, lid, header["incomingRefs"] as ArrayCollection);
            PersistVO(cid, lid, vo);
        }

        protected void PersistRefs(int cid, int lid, ArrayCollection refs)
        {
            string delSql = "DELETE FROM {0}_Refs WHERE cid={1} AND lid={2}";
            delSql = String.Format(delSql, tablePrefix, cid, lid);
            ExecuteNQ(delSql);

            foreach (ASObject refASO in refs)
            {
                int sourceCid = Convert.ToInt32(refASO["clientID"]);
                int sourceLid = Convert.ToInt32(refASO["localID"]);
                double itemIndex = Convert.ToDouble(refASO["index"]);
                string propName = MakeDBSafe(refASO["property"]);
                string insSql = "INSERT INTO {0}_Refs (cid,lid,sourceCid,sourceLid,propName,itemIndex) ";
                insSql += "VALUES ({1},{2},{3},{4},{5},{6})";
                insSql = String.Format(insSql, tablePrefix, cid, lid, sourceCid, sourceLid, propName, itemIndex);
                ExecuteNQ(insSql);
            }
        }
        protected void PersistVO(int cid, int lid, ASObject vo)
        {
            string delSql = "DELETE FROM {0}_Props WHERE cid={1} AND lid={2}";
            delSql = String.Format(delSql, tablePrefix, cid, lid);
            ExecuteNQ(delSql);

            foreach (string propName in vo.Keys)
            {
                string insSql = "INSERT INTO {0}_Props (cid,lid,propName,propVal) ";
                insSql += "VALUES ({1},{2},{3},{4})";
                insSql = String.Format(insSql, tablePrefix, cid, lid, MakeDBSafe(propName), MakeDBSafe(vo[propName]));
                ExecuteNQ(insSql);
            }
        }

        protected DataTable ExecuteDT(string sqlString)
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();

            SQLiteCommand command = new SQLiteCommand(sqlString, conn);
            SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            return dt;
        }
        protected int ExecuteNQ(string sqlString)
        {
            if (conn.State == ConnectionState.Closed)
                conn.Open();

            SQLiteCommand command = new SQLiteCommand(sqlString, conn);
            return command.ExecuteNonQuery();
        }
        protected string MakeDBSafe(object source)
        {
            if (source == null)
                return "''";
            if (source is bool)
                return (bool)source ? "'1'" : "'0'";
            if (source is DateTime)
                return "'" + ((DateTime)source).ToString() + "'";

            string transf = source.ToString();
            transf = transf.Replace("'", @"''");
            return "'" + transf + "'";
        }
    }
}
